package javamusic.DAO;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import javamusic.excepcion.DAOExcepcion;
import javamusic.excepcion.LoginExcepcion;
import javamusic.modelo.Cliente;
import javamusic.modelo.Local;
import javamusic.modelo.Sala;

import javamusic.util.ConexionBDKaraoke;

public class ClienteDAO extends  BaseDAO{
	/*
	 * INGRESAR CLIENTES
	 */
	
	
	public Cliente insertar(Cliente vo) throws DAOExcepcion {
		String query = "INSERT INTO cliente (idcliente, correo,clave,telefono,dni) "
				+ "VALUES (?,?,?,?,?)";
		System.err.println(query);
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = ConexionBDKaraoke.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setInt(1, vo.getIdCliente());
			stmt.setString(2, vo.getCorreo());
			stmt.setString(3, vo.getClave());
			stmt.setInt(4, vo.getTelefono());
			System.out.println(".tel.. " + vo.getTelefono() );
			stmt.setInt(5, vo.getDni());
			
			
			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo insertar");
			}
			
			int idcliente = 0;
			query = "SELECT LAST_INSERT_ID()";
			stmt = con.prepareStatement(query);
			rs = stmt.executeQuery();
			if (rs.next()) {
				idcliente = rs.getInt(1);
			}
			vo.setIdCliente(idcliente);
			
			
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return vo;
	}
	
	
	
	
	
	
	
	
	
	
	/*
	 * s
	 */
	
	public Collection<Cliente> listar() throws DAOExcepcion {
		Collection<Cliente> c = new ArrayList<Cliente>();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = ConexionBDKaraoke.obtenerConexion();
			String query = "select idcliente,c.dni,nombres,paterno,materno from cliente c inner join persona p on (c.dni = p.dni) order by nombres,paterno";
			stmt = con.prepareStatement(query);
			rs = stmt.executeQuery();
			while (rs.next()) {
				Cliente vo = new Cliente();
				vo.setIdCliente(rs.getInt("idcliente"));
				vo.setDni(rs.getInt("dni"));
				vo.setNombre(rs.getString("nombres"));
				vo.setApePaterno(rs.getString("paterno"));
				vo.setApeMaterno(rs.getString("materno"));
				
				
				
				c.add(vo);
			}

		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return c;
	}
	

	/*
	 * OBETNET SALA
	 */
	
	public Cliente obtenerpornombre(String nombre )throws DAOExcepcion {
		System.out.println("GestionSala: obtener(int salaid)");
		Cliente vo = new Cliente();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String query = "select * from local where nombre=?";
			
			con = ConexionBDKaraoke.obtenerConexion();
			stmt = con.prepareStatement(query);
			//stmt.setInt(1, idsala);
			stmt.setString(1,nombre);
			
			//stmt.setInt(1, idsla);
			rs = stmt.executeQuery();
			if (rs.next()) {
				
				
				vo.setIdlocal(rs.getInt("idlocal"));
				vo.setNombre(rs.getString("nombre"));
				vo.setDireccionlocal(rs.getString("direccion"));
				vo.setTelefonolocal(rs.getString("telefono"));
				vo.setCorreo(rs.getString("correo"));
				vo.setDescripcionlocal(rs.getString("descripcion"));
				vo.setIdadministrador(rs.getInt("idadministrador"));
				
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
		
			this.cerrarConexion(con);
		}
		return vo;
		}
	
	/*
	 * 
	 */
	
	public Cliente obtenerpornombresala(String nombresala )throws DAOExcepcion {
		System.out.println("GestionSala: obtener(int salaid)");
		Cliente vo = new Cliente();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String query = "select sala.nombre,sala.capacidad,sala.descripcion,local.nombre  from sala , local where sala.idlocal= local.idlocal and sala.nombre=?";
			
			con = ConexionBDKaraoke.obtenerConexion();
			stmt = con.prepareStatement(query);
			//stmt.setInt(1, idsala);
			stmt.setString(1,nombresala);
			
			//stmt.setInt(1, idsla);
			rs = stmt.executeQuery();
			if (rs.next()) {
				
				//vo.setNombre(rs.getString("sala.nombre"));
				vo.setNombrelocal(rs.getString("local.nombre"));
				
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
		
			this.cerrarConexion(con);
		}
		return vo;
		}
	
	/*
	 * 
	 */
	
	
	public Cliente obtenerpornombrecorreo (String correo )throws DAOExcepcion {
		System.out.println("GestionSala: obtener(int salaid)");
		Cliente vo = new Cliente();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String query = "select t0.idcliente,t1.nombres,t1.paterno,t0.dni from cliente t0 inner join persona t1 on t0.dni=t1.dni where t0.correo =?";
			
			con = ConexionBDKaraoke.obtenerConexion();
			stmt = con.prepareStatement(query);
			//stmt.setInt(1, idsala);
			stmt.setString(1,correo);
			
			//stmt.setInt(1, idsla);
			rs = stmt.executeQuery();
			if (rs.next()) {
				
				//vo.setNombre(rs.getString("sala.nombre"));
				vo.setNombre(rs.getString("t1.nombres"));
				vo.setDni(rs.getInt("t0.dni"));
				
				
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
		
			this.cerrarConexion(con);
		}
		return vo;
		}
	
	
	
	
	/*
	 * 
	 */
	
	
	
	public Cliente validar(String correo, String clave)
			throws DAOExcepcion, LoginExcepcion {
		String query = "select correo,  clave,"
				+ " telefono"
				+ " from cliente where correo=? and clave=?";
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Cliente vo = new Cliente();
		try {
			con = ConexionBDKaraoke.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setString(1, correo);
			stmt.setString(2, clave);
			rs = stmt.executeQuery();

			if (rs.next()) {
				vo.setCorreo(rs.getString("correo"));
				vo.setClave(rs.getString("clave"));
				vo.setTelefono(rs.getInt("telefono"));
								
			} else {
				throw new LoginExcepcion("No existe");
			}
		} catch (LoginExcepcion e) {
			System.err.println(e.getMessage());
			throw new LoginExcepcion(e.getMessage());
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return vo;
		
		
		
		
	}
	
	
	
	
	
	

}
